package br.com.egg.finances.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import br.com.egg.finances.business.dao.util.BDUtils;
import br.com.egg.finances.business.exception.BusinessErrorException;
import br.com.egg.finances.model.Conta;

public class ContaDAO extends DefaultDAO<Conta> {

	@Override
	public void insert(Conta entity) {
		Connection connection = null;
		CallableStatement callableStatement = null;
		try {
			StringBuilder sbCall = new StringBuilder();
			sbCall.append("{ CALL inserir_conta(?, ?, ?, ?, ?, ?, ?, ?) }");
			
			String call = sbCall.toString();
			System.out.println(call);
			
			connection = BDUtils.getConnection();
			callableStatement = connection.prepareCall(call);
			callableStatement.setString(1, entity.getCdUsuario());
			callableStatement.setString(2, entity.getCdConta());
			
			if (entity.getCdBanco() == null) {
				callableStatement.setNull(3, Types.INTEGER);
			} else {
				callableStatement.setInt(3, entity.getCdBanco());
			}
			
			callableStatement.setString(4, entity.getNuConta());
			callableStatement.setString(5, entity.getNuAgencia());
			callableStatement.setString(6, entity.getDeConta());
			callableStatement.setDouble(7, entity.getVlSaldo());
			callableStatement.registerOutParameter(8, Types.VARCHAR);
			System.out.println("-> " + callableStatement.toString());
			
			callableStatement.execute();
			
			
			
			validaChamdaProcedure(callableStatement, 8);
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, callableStatement, null);
		}
	}

	@Override
	public void update(Conta entity) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			StringBuilder sbUpdate = new StringBuilder();
			sbUpdate.append("UPDATE DEGGCONTA CONTA ");
			sbUpdate.append("SET CONTA.CDBANCO = ?, CONTA.NUCONTA = ?, CONTA.NUAGENCIA = ?, CONTA.DECONTA = ? ");
			sbUpdate.append("WHERE CONTA.CDUSUARIO = ? AND CONTA.CDCONTA = ? ");
			
			String update = sbUpdate.toString();
			System.out.println(update);
			
			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(update);
			
			if (entity.getCdBanco() != null) {
				preparedStatement.setInt(1, entity.getCdBanco());
			} else {
				preparedStatement.setNull(1, Types.INTEGER);
			}
			
			preparedStatement.setString(2, entity.getNuConta());
			preparedStatement.setString(3, entity.getNuAgencia());
			preparedStatement.setString(4, entity.getDeConta());
			preparedStatement.setString(5, entity.getCdUsuario());
			preparedStatement.setString(6, entity.getCdConta());
			
			preparedStatement.executeUpdate();
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, null);
		}
	}

	@Override
	public void delete(Conta entity) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			StringBuilder sbUpdate = new StringBuilder();
			sbUpdate.append("DELETE FROM DEGGCONTA ");
			sbUpdate.append("WHERE CDUSUARIO = ? AND CDCONTA = ? ");
			
			String delete = sbUpdate.toString();
			System.out.println(delete);
			
			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(delete);
			preparedStatement.setString(1, entity.getCdUsuario());
			preparedStatement.setString(2, entity.getCdConta());
			
			preparedStatement.executeUpdate();
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, null);
		}
	}

	@Override
	public Conta find(Conta entityFilter) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			StringBuilder sbQuery = new StringBuilder();
			sbQuery.append("SELECT CONTA.CDUSUARIO AS CDUSUARIO, CONTA.CDCONTA AS CDCONTA, CONTA.CDBANCO AS CDBANCO, ");
			sbQuery.append("CONTA.NUCONTA AS NUCONTA, CONTA.NUAGENCIA AS NUAGENCIA, CONTA.DECONTA AS DECONTA, CONTA.VLSALDO AS VLSALDO ");
			sbQuery.append("FROM DEGGCONTA CONTA ");
			sbQuery.append("WHERE CDCONTA = ? AND CDUSUARIO = ? ");
			
			String query = sbQuery.toString();
			System.out.println(query);
			
			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, entityFilter.getCdConta());
			preparedStatement.setString(2, entityFilter.getCdUsuario());
			
			resultSet = preparedStatement.executeQuery();
			
			Conta conta = null;
			
			if (resultSet.next()) {
				conta = new Conta();
				conta.setCdUsuario(resultSet.getString("CDUSUARIO"));
				conta.setCdConta(resultSet.getString("CDCONTA"));
				conta.setCdBanco(resultSet.getInt("CDBANCO"));
				conta.setNuConta(resultSet.getString("NUCONTA"));
				conta.setNuAgencia(resultSet.getString("NUAGENCIA"));
				conta.setDeConta(resultSet.getString("NUCONTA"));
				conta.setVlSaldo(resultSet.getDouble("VLSALDO"));
			}
			
			return conta;
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, resultSet);
		}
	}

	@Override
	public List<Conta> findAll(String cdUsuario) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			StringBuilder sbQuery = new StringBuilder();
			sbQuery.append("SELECT CONTA.CDUSUARIO AS CDUSUARIO, CONTA.CDCONTA AS CDCONTA, CONTA.CDBANCO AS CDBANCO, ");
			sbQuery.append("CONTA.NUCONTA AS NUCONTA, CONTA.NUAGENCIA AS NUAGENCIA, CONTA.DECONTA AS DECONTA, CONTA.VLSALDO AS VLSALDO ");
			sbQuery.append("FROM DEGGCONTA CONTA ");
			sbQuery.append("WHERE CONTA.CDUSUARIO = ? ");
			
			String query = sbQuery.toString();
			System.out.println(query);
			
			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, cdUsuario);
			
			resultSet = preparedStatement.executeQuery();
			
			List<Conta> contaList = new ArrayList<Conta>();
			
			if (resultSet.next()) {
				Conta conta = new Conta();
				conta.setCdUsuario(resultSet.getString("CDUSUARIO"));
				conta.setCdConta(resultSet.getString("CDCONTA"));
				conta.setCdBanco(resultSet.getInt("CDBANCO"));
				conta.setNuConta(resultSet.getString("NUCONTA"));
				conta.setNuAgencia(resultSet.getString("NUAGENCIA"));
				conta.setDeConta(resultSet.getString("NUCONTA"));
				conta.setVlSaldo(resultSet.getDouble("VLSALDO"));
				
				contaList.add(conta);
			}
			
			return contaList;
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, resultSet);
		}
	}

}
